package com.atguigu.xuexiplus.mapper;

import com.atguigu.xuexiplus.dto.HomeworkVO;
import com.atguigu.xuexiplus.entity.Homework;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 作业Mapper接口
 */
@Mapper
public interface HomeworkMapper extends BaseMapper<Homework> {

    /**
     * 根据学科ID查询作业列表（包含学科信息）
     */
    @Select("SELECT h.*, s.name as subject_name, s.icon as subject_icon, s.color as subject_color, " +
            "u.nickname as teacher_name " +
            "FROM homework h " +
            "LEFT JOIN subject s ON h.subject_id = s.id " +
            "LEFT JOIN user u ON h.teacher_id = u.id " +
            "WHERE h.subject_id = #{subjectId} AND h.status = 1 AND h.deleted = 0 " +
            "ORDER BY h.priority DESC, h.publish_time DESC")
    List<HomeworkVO> getHomeworksBySubjectId(@Param("subjectId") Long subjectId);

    /**
     * 查询今日作业列表（包含学科信息）
     */
    @Select("SELECT h.*, s.name as subject_name, s.icon as subject_icon, s.color as subject_color, " +
            "u.nickname as teacher_name " +
            "FROM homework h " +
            "LEFT JOIN subject s ON h.subject_id = s.id " +
            "LEFT JOIN user u ON h.teacher_id = u.id " +
            "WHERE h.status = 1 AND h.deleted = 0 " +
            "ORDER BY h.priority DESC, h.publish_time DESC")
    List<HomeworkVO> getTodayHomeworks();

    /**
     * 根据学生ID和学科ID查询作业完成情况
     */
    @Select("SELECT h.*, s.name as subject_name, s.icon as subject_icon, s.color as subject_color, " +
            "u.nickname as teacher_name, COALESCE(hc.completion_status, 0) as completion_status " +
            "FROM homework h " +
            "LEFT JOIN subject s ON h.subject_id = s.id " +
            "LEFT JOIN user u ON h.teacher_id = u.id " +
            "LEFT JOIN homework_completion hc ON h.id = hc.homework_id AND hc.student_id = #{studentId} " +
            "WHERE h.subject_id = #{subjectId} AND h.status = 1 AND h.deleted = 0 " +
            "ORDER BY h.priority DESC, h.publish_time DESC")
    List<HomeworkVO> getHomeworksBySubjectIdAndStudent(@Param("subjectId") Long subjectId, @Param("studentId") Long studentId);

    /**
     * 查询今日所有作业（含完成状态，按学生）
     */
    @Select("SELECT h.*, s.name as subject_name, s.icon as subject_icon, s.color as subject_color, " +
            "u.nickname as teacher_name, COALESCE(hc.completion_status, 0) as completion_status " +
            "FROM homework h " +
            "LEFT JOIN subject s ON h.subject_id = s.id " +
            "LEFT JOIN user u ON h.teacher_id = u.id " +
            "LEFT JOIN homework_completion hc ON h.id = hc.homework_id AND hc.student_id = #{studentId} " +
            "WHERE h.status = 1 AND h.deleted = 0 " +
            "ORDER BY h.priority DESC, h.publish_time DESC")
    List<HomeworkVO> getTodayHomeworksByStudent(@Param("studentId") Long studentId);
}
